package postpc.powerreminders.com.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class RotgDatabase extends SQLiteOpenHelper {

public static final String TAG = "ROTG_Database";
	
	// Database fields
	public static final int DATABASE_VERSION = 1;
	public static final String DATABASE_NAME = "ROTG_DB";

	// Field variables
	public static final String REMINDERS_TABLE = "reminders";
	public static final String LOCATIONS_TABLE = "locations";
	public static final String KEY_ROWID = "_id";
	public static final String NAME = "name";
	public static final String MESSAGE = "message";
	public static final String GPS_LONGITUDE = "lon";
	public static final String GPS_LATITUDE = "lat";
	public static final String WIFI_MAC = "bssid";
	public static final String ROTG_ACTION = "action";
	public static final String LOCATION_ID = "location_id";
	public static final String APPLICATION = "application";
	public static final String PHONE_NUMBER = "phone_number";
	public static final String WIFI_SSID = "ssid";
	public static final String ACTIVE = "active";
	
	public static final int REMINDER_DISABLED = 0;
	public static final int REMINDER_ACTIVE = 1;

	// Static instance
	private static RotgDatabase DBInstance;
	
	// Private fields
	private SQLiteDatabase m_Database;
	
	// ROTG_ACTION fields
	public static final int ACTION_LOCATION 	= 0;
	public static final int ACTION_PHONE 		= 1;
	public static final int ACTION_APP		 	= 2;

	// Locations data base structure 
	private static final String LOCATIONS_CREATE = 
		"CREATE TABLE " + LOCATIONS_TABLE + "(" + 
		KEY_ROWID + " integer primary key autoincrement, " +
		NAME + " text not null, " +
		GPS_LONGITUDE + " double, " +
		GPS_LATITUDE + " double, " +
		WIFI_MAC + " text, " +
		WIFI_SSID + " text);";
	
	// Reminders data base structure 
	private static final String REMINDERS_CREATE = 
		"create table " + REMINDERS_TABLE +
		"(" + KEY_ROWID + " integer primary key autoincrement, "
		+ LOCATION_ID + " integer, "
		+ NAME + " text not null, "
		+ MESSAGE + " text not null, "
		+ APPLICATION +" text, "
		+ PHONE_NUMBER + " text, "
		+ ACTIVE + " integer, "
		+ ROTG_ACTION + " integer, "
	    + "foreign key("+ LOCATION_ID +") references " + LOCATIONS_TABLE + "(" + KEY_ROWID + ") " +
	    ");";

	// Constructors
	public RotgDatabase(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	
	public RotgDatabase(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	/**
	 * Called on the first database creation
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.i(TAG, "Creating the database : " + REMINDERS_CREATE);	
		db.execSQL(REMINDERS_CREATE);
		
		Log.i(TAG, "Creating the database : " + LOCATIONS_CREATE);	
		db.execSQL(LOCATIONS_CREATE);

		this.m_Database = db;
		RotgDatabase.DBInstance = this;
		
		
		// TODO: Remove for full update
		long location1 = newLocation("Gilad's House",  35.21293, 31.774419, "00:0f:34:49:9f:d3", "Gilad's House");
		long location2 = newLocation("Mazkirot", 0, 0, "00:0f:34:49:9f:d3", "Levi Building");
		
		newReminder(0, "Gmail Notification", "Send mail to barack", "com.google.android.gm", 
				"", RotgDatabase.REMINDER_DISABLED, RotgDatabase.ACTION_APP);	
		
		newReminder(location1, "Get Book", "Don't forget to take the Infi Book",
				"", "", RotgDatabase.REMINDER_DISABLED, RotgDatabase.ACTION_LOCATION);
		
		newReminder(0, "barack", "Ask Obama: WE CAN ?", "", "12212345678", 
				RotgDatabase.REMINDER_DISABLED, RotgDatabase.ACTION_PHONE);
		
		newReminder(location2, "Mazkirot", "Dont forget to pick up Form!", "", "", 
				RotgDatabase.REMINDER_DISABLED, RotgDatabase.ACTION_LOCATION);
		
		Log.i(TAG, "Created databse in " + db.getPath());
	}

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		Log.w(TAG, "onUpgrade of the database");
	}
	
	/**
	 * Opens the database for use.
	 * 
	 * @return
	 * @throws SQLException
	 */
	public void open() throws SQLException {
		//Log.i(TAG, "Openning the ROTG database");
		
		if (this.m_Database == null || !this.m_Database.isOpen()) {
			this.m_Database = getWritableDatabase();
		}
		
		RotgDatabase.DBInstance = this;
	}

	/**
	 * Closes the database after use.
	 * 
	 * @return
	 * @throws SQLException
	 */
	public void close() {
		Log.i(TAG, "Closing Database");
		super.close();
	}
	
	/**
	 * 
	 * @param locationId
	 * @param name
	 * @param message
	 * @param action
	 * @return
	 */
	public long newReminder(long locationId, String name, String message, String application,
				String phonenumber,int active, int action) {
		this.open();
		Log.i(TAG, "Creating new reminder for : " + locationId);
		ContentValues newReminder = createReminderContentValues(
														locationId, 
														name, 
														message,
														application,
														phonenumber,
														active,
														action);
		return m_Database.insert(REMINDERS_TABLE, null, newReminder);
	}
	
	/**
	 * Push a new reminder to the database.
	 */
	public long newLocation(String locationName, 
									double lon, double lat, String mac, String ssid) {
		ContentValues newLocation = createLocationContentValues(
														locationName, 
														lon, 
														lat,
														mac,
														ssid);

		return m_Database.insert(LOCATIONS_TABLE, null, newLocation);
	}

	/**
	 * Update a reminder in the reminders table
	 */
	public boolean updateReminder(long rowId, long locationId, String name, 
			String message, String applicaiton, String phonenumber, int active, int action) {
		this.open();
		Log.i(TAG, "Updateing reminder #" + rowId);
		ContentValues updateValues = createReminderContentValues(
														locationId, 
														name, 
														message,
														applicaiton,
														phonenumber,
														active,
														action);

		return m_Database.update(REMINDERS_TABLE, updateValues, KEY_ROWID + "="
				+ rowId, null) > 0;
	}
	
	/**
	 * Update a location in the locations table
	 */
	public boolean updateLocation(long rowId, String name, double lon, 
			double lat,	String mac, String ssid) {
		this.open();
		Log.i(TAG, "Updateing location #" + rowId);
		ContentValues updateValues = createLocationContentValues(
														name, 
														lon, 
														lat, 
														mac,
														ssid);

		return m_Database.update(LOCATIONS_TABLE, updateValues, KEY_ROWID + "="
				+ rowId, null) > 0;
	}

	/**
	 * Deletes a reminder from the reminders table
	 */
	public boolean deleteReminder(long rowId) {
		this.open();
		Log.i(TAG, "Deleting reminder #" + rowId);
		return m_Database.delete(REMINDERS_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
	}
	
	/**
	 * Deletes a location from the locations table
	 * @param id
	 * @return 
	 */
	public boolean deleteLocation(long rowId) {
		this.open();
		Log.i(TAG, "Deleting location #" + rowId);
		m_Database.delete(LOCATIONS_TABLE, KEY_ROWID + "=" + rowId, null);
		m_Database.delete(REMINDERS_TABLE, LOCATION_ID + "=" + rowId, null);
		return true;
	}

	/**
	 * Return a Cursor over the list of all reminders in the database
	 * 
	 * @return Cursor over all reminders
	 */
	public Cursor fetchAllReminders() {
		this.open();
		return m_Database.query(REMINDERS_TABLE, new String[] { KEY_ROWID, LOCATION_ID,
				NAME, MESSAGE, APPLICATION, PHONE_NUMBER, ACTIVE, ROTG_ACTION}, 
				null, null, null, null, null);
	}
	
	/**
	 * Pulls from the database all the reminders that have a GPS data.
	 *  
	 * @return
	 */
	public Cursor fetchAll_GPS_Reminders() {
		this.open();
		Log.i(TAG, "Fetcing all GPS related database reminders");
		Cursor cursor = m_Database.query(true, LOCATIONS_TABLE, new String[] { KEY_ROWID,
				NAME, GPS_LONGITUDE, GPS_LATITUDE}, GPS_LATITUDE + "!=null",
				null, null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}
	
	public Cursor fetchAllLocations() {
		this.open();
		Cursor cursor=m_Database.query(LOCATIONS_TABLE, new String[] { KEY_ROWID,
				NAME, GPS_LONGITUDE, GPS_LATITUDE, WIFI_MAC}, 
				null, null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}

	/**
	 * Return a Cursor positioned at the defined reminder
	 */
	public Cursor fetchLocation(long rowId) throws SQLException {
		this.open();
		Cursor mCursor = m_Database.query(true, LOCATIONS_TABLE, new String[] { KEY_ROWID,
				NAME,GPS_LONGITUDE, GPS_LATITUDE, WIFI_MAC, WIFI_SSID},
				KEY_ROWID + "=" + rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	/**
	 * Return a Cursor positioned at the defined reminder
	 */
	public Cursor fetchReminder(long rowId) throws SQLException {
		this.open();
		Cursor mCursor = m_Database.query(true, REMINDERS_TABLE, new String[] { KEY_ROWID, LOCATION_ID,
				NAME, MESSAGE, APPLICATION, PHONE_NUMBER, ACTIVE, ROTG_ACTION},
				KEY_ROWID + "=" + rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	/**
	 * Return a Cursor positioned at the defined reminder
	 */
	public Cursor fetchReminderbyLocation(long locationId) throws SQLException {
		this.open();
		Cursor mCursor = m_Database.query(true, REMINDERS_TABLE, new String[] { KEY_ROWID, LOCATION_ID,
				NAME, MESSAGE, APPLICATION, PHONE_NUMBER, ACTIVE, ROTG_ACTION},
				LOCATION_ID + "=" + locationId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}
	
	/**
	 * 
	 * Check if a MAC address is in the database. 
	 * 
	 * @param macAddress
	 * @return
	 */
	public Cursor checkMacAddress(String macAddress){
		this.open();
		//Log.i(TAG, "checking if MAC address " + macAddress + " is in the database");
		Cursor cursor = this.m_Database.query(true, LOCATIONS_TABLE, new String[] { KEY_ROWID,
				WIFI_MAC}, WIFI_MAC + "=\'" + macAddress + "\'", 
				null, null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}
	
	/**
	 * 
	 * @param processName
	 * @return
	 */
	public Cursor checkApplication(String processName) {
		this.open();
		Log.i(TAG, "checking if application " + processName + " is in the databse");
		Cursor cursor = this.m_Database.query(true, REMINDERS_TABLE, new String[] {  KEY_ROWID, LOCATION_ID,
				NAME, MESSAGE, APPLICATION, PHONE_NUMBER, ACTIVE, ROTG_ACTION}, 
				APPLICATION + "=\'" + processName + "\' AND " +	
				ROTG_ACTION + "=" + ACTION_APP + " AND " + ACTIVE + "=" + REMINDER_ACTIVE, 
				null, null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}
	
	/**
	 * 
	 * @param phonenumber
	 * @return
	 */
	public Cursor checkCallEvent(String phonenumber) {
		this.open();
		Log.i(TAG, "checking if phonenumber " + phonenumber + " is in the databse");
		Cursor cursor = this.m_Database.query(true, REMINDERS_TABLE, new String[] {  KEY_ROWID, LOCATION_ID,
				NAME, MESSAGE, APPLICATION, PHONE_NUMBER, ACTIVE, ROTG_ACTION}, 
				PHONE_NUMBER + "=\'" + phonenumber + "\' AND " +
				ROTG_ACTION + "=" + ACTION_PHONE + " AND " + 
				ACTIVE + "=" + REMINDER_ACTIVE, 
				null, null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}
	
	/**
	 * reminder
	 * @param index
	 */
	public void deactivateReminder(long index) {
		this.open();
		Cursor cursor = this.fetchReminder(index);
		if (cursor.getCount() != 0){
			this.updateReminder(
					cursor.getLong(0), 
					cursor.getLong(1),
					cursor.getString(2), 
					cursor.getString(3), 
					cursor.getString(4), 
					cursor.getString(5), 
					REMINDER_DISABLED, 
					cursor.getInt(7));
		}
		
	}
	
	/**
	 * reminder
	 * @param index
	 */
	public void deactivateReminderByLocation(long index) {
		this.open();
		Cursor cursor = this.fetchReminderbyLocation(index);
		if (cursor.getCount() != 0){
			this.updateReminder(
					cursor.getLong(0), 
					cursor.getLong(1),
					cursor.getString(2), 
					cursor.getString(3), 
					cursor.getString(4), 
					cursor.getString(5), 
					REMINDER_DISABLED, 
					cursor.getInt(7));
		}
		
	}
	
	/**
	 * 
	 * @param index
	 */
	public void activateReminder(long index) {
		this.open();
		Cursor cursor = this.fetchReminder(index);
		if (cursor.getCount() != 0){
			this.updateReminder(
					cursor.getInt(0), 
					cursor.getInt(1),
					cursor.getString(2), 
					cursor.getString(3), 
					cursor.getString(4), 
					cursor.getString(5), 
					REMINDER_ACTIVE, 
					cursor.getInt(7));
		}
		
	}

	/*
	 * Content values
	 */
	private ContentValues createReminderContentValues(long locationId,
					String name, String message, String application, String phonenumber, int active, int action) {
		ContentValues values = new ContentValues();
		values.put(LOCATION_ID, locationId);
		values.put(NAME, name);
		values.put(MESSAGE, message);
		values.put(APPLICATION, application);
		values.put(PHONE_NUMBER, phonenumber);
		values.put(ACTIVE, active);
		values.put(ROTG_ACTION, action);
		return values;
	}

	private ContentValues createLocationContentValues(String name, double lon, double lat, String mac, String ssid) {
		ContentValues values = new ContentValues();
		values.put(NAME, name);
		values.put(GPS_LONGITUDE, lon);
		values.put(GPS_LATITUDE, lat);
		values.put(WIFI_MAC, mac);
		values.put(WIFI_SSID, ssid);
		return values;
	}

	public static RotgDatabase getInstance() {
		return DBInstance;
	}
	
	public static boolean print = true;
	/**
	 * 
	 */
	public void printDataBase(){
		Cursor cursor = fetchAllReminders();
		if (!print){
			return;
		}
		if (cursor != null) {
			cursor.moveToFirst();
		}
		while (!cursor.isAfterLast()){
			Log.i(TAG, " -------- ");
			Log.i(TAG, "KEY_ROWID  " + cursor.getInt(0));
			Log.i(TAG, "LOCATION_ID  " + cursor.getInt(1));
			Log.i(TAG, "NAME  " + cursor.getString(2));
			Log.i(TAG, "MESSAGE  " + cursor.getString(3));
			Log.i(TAG, "APPLICATION  " + cursor.getString(4));
			Log.i(TAG, "ACTIVE  " + cursor.getInt(5));
 			Log.i(TAG, "ROTG_ACTION  " + cursor.getInt(6));
 			cursor.moveToNext();
		}
		
		cursor = fetchAllLocations();
		if (!print){
			return;
		}
		if (cursor != null) {
			cursor.moveToFirst();
		}
		while (!cursor.isAfterLast()){
			Log.i(TAG, " -------- ");
			Log.i(TAG, "KEY_ROWID  " + cursor.getInt(0));
			Log.i(TAG, "NAME  " + cursor.getString(1));
			Log.i(TAG, "GPS_LONGITUDE  " + cursor.getDouble(2));
			Log.i(TAG, "GPS_LATITUDE  " + cursor.getDouble(3));
			Log.i(TAG, "WIFI_MAC  " + cursor.getString(4));
			//Log.i(TAG, "WIFI_SSID  " + cursor.getString(5));
			
 			cursor.moveToNext();
		}
		print = false;
	}
}